package railRoad.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import railRoad.Passenger;
import railRoad.Ticket;
import railRoad.dao.interfaces.IDAO;
import railRoad.db.DBConectionPool;
import railRoad.exception.DBException;

/**
 * Implementation of {@link#IDAO} for {@link#Passenger}
 * 
 * @see IDAO
 * @see Passenger
 * @author Rukin
 *
 */
public class PassangerDAO implements IDAO<Passenger> {

	private DBConectionPool conectionPool = DBConectionPool.getInstance();

	public Passenger select(int id) throws DBException {

		Passenger human = null;
		Connection connection = null;
		try {
			connection = conectionPool.getConnection();
			connection.setAutoCommit(false);

			Statement statement = connection.createStatement();
			ResultSet resultSet = statement
					.executeQuery("SELECT * FROM PASSANGER  WHERE PASSANGER_ID = "
							+ id);
			int hId = -1;
			String surname = null;
			String name = null;
			ArrayList<Ticket> tickets = new ArrayList<Ticket>();

			while (resultSet.next()) {
				hId = resultSet.getInt("PASSANGER_ID");
				surname = resultSet.getString("SURNAME");
				name = resultSet.getString("NAME");
			}

			resultSet = statement
					.executeQuery("SELECT * FROM TICKET where PASSANGER_ID = "
							+ id);

			while (resultSet.next()) {
				int tId = resultSet.getInt("TICKET_ID");
				int ttId = resultSet.getInt("TICKET_TYPE_ID");
				int pId = resultSet.getInt("PASSANGER_ID");
				int rId = resultSet.getInt("ROUTE_ID");
				double price = resultSet.getDouble("Price");

				tickets.add(new Ticket(tId, pId, rId, ttId, price));
			}

			human = new Passenger(hId, tickets, surname, name);

		} catch (SQLException e) {
			throw new DBException(e.getMessage());
		} finally {
			conectionPool.close(connection);
		}// finally

		return human;
	}

	public Passenger select(String field) throws DBException {
		return null;
	}

	public int insert(Passenger human, int parrentId) throws DBException {
		Connection connection = null;
		int result = -1;
		try {
			connection = conectionPool.getConnection();
			connection.setAutoCommit(false);

			PreparedStatement preparedStatement = connection
					.prepareStatement("INSERT INTO PASSANGER( NAME , SURNAME ) VALUES (?,?)");

			preparedStatement.setString(1, human.getName());
			preparedStatement.setString(2, human.getSurname());

			result = preparedStatement.executeUpdate();

			ResultSet resultSet = connection.createStatement().executeQuery(
					"SELECT MAX(PASSANGER_ID) as ID FROM PASSANGER");
			int id = -1;
			while (resultSet.next()) {
				id = resultSet.getInt("ID");
			}

			human.setPassenger_id(id);

			connection.commit();

		} catch (SQLException e) {
			throw new DBException(e.getMessage());
		} finally {
			conectionPool.close(connection);
		}// finally

		return result;
	}

	public int update(Passenger passanger) throws DBException {
		Connection connection = null;

		int result = -1;
		try {
			connection = conectionPool.getConnection();
			connection.setAutoCommit(false);

			PreparedStatement preparedStatement = connection
					.prepareStatement("update passanger p "
							+ "set p.name =?, p.surname = ? "
							+ "where p.passanger_id = ?");

			preparedStatement.setString(1, passanger.getName());
			preparedStatement.setString(2, passanger.getSurname());
			preparedStatement.setInt(3, passanger.getPassenger_id());

			result = preparedStatement.executeUpdate();

			connection.commit();
		} catch (SQLException e) {
			throw new DBException(e.getMessage());
		} finally {
			conectionPool.close(connection);
		}// finally

		return result;
	}

	public int delete(Passenger passenger) throws DBException {
		Connection connection = null;

		int result = -1;
		try {
			connection = conectionPool.getConnection();

			int id = passenger.getPassenger_id();

			PreparedStatement preparedStatement = connection
					.prepareStatement("delete from systemuser where passanger_id = "
							+ id
							+ "; delete from passanger where passanger_id = "
							+ id);

			result = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			throw new DBException(e.getMessage());
		} finally {
			conectionPool.close(connection);
		}// finally

		return result;
	}// delete

}
